﻿using NPOI.SS.Formula.Functions;
using RemoteTool.Business.Tools.Helper;
using RemoteTool.Data.Model;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Data.SQLite;
using System.IO;
using System.Linq;
using System.Linq.Expressions;
using System.Threading.Tasks;

namespace RemoteTool.IRepositories
{
    public class SqliteRepository : IRepository<DeviceInfo>
    {
        public string DbFilePath { get { return $"{AppDomain.CurrentDomain.BaseDirectory}DataBase\\RemoteTool.db"; } }
        public SqlSugarClient Db
        {
            get
            {
                return new SqlSugarClient(new ConnectionConfig()
                {
                    ConnectionString = $"DataSource={DbFilePath}",
                    DbType = DbType.Sqlite,
                    IsAutoCloseConnection = true,
                    InitKeyType = InitKeyType.Attribute
                });
            }
        }

        public SqliteRepository()
        {
            DbInit();
        }
        private void DbInit()
        {
            try
            {
                LogHelper.WriteLog("开始数据库初始化！");
                var path = $"{AppDomain.CurrentDomain.BaseDirectory}DataBase";
                if (Directory.Exists(path) == false)
                {
                    Directory.CreateDirectory(path);
                }
                if (File.Exists(DbFilePath) == false)
                {
                    SQLiteConnection.CreateFile(DbFilePath);
                    Db.CodeFirst.InitTables(typeof(DeviceInfo));
                }
                else
                {
                    if (!Db.DbMaintenance.GetTableInfoList().Exists(p => p.Name == nameof(DeviceInfo)))
                    {
                        Db.CodeFirst.InitTables(typeof(DeviceInfo));
                    }
                    if (!Db.DbMaintenance.IsAnyColumn(nameof(DeviceInfo), "GuidCode"))
                    {
                        var column = new DbColumnInfo();
                        column.IsNullable = false;
                        column.DbColumnName = "GuidCode";
                        column.TableName = nameof(DeviceInfo);
                        Db.DbMaintenance.AddColumn(nameof(DeviceInfo), column);
                    }
                    if (!Db.DbMaintenance.IsAnyColumn(nameof(DeviceInfo), "GuidCode"))
                    {
                        var column = new DbColumnInfo();
                        column.IsNullable = false;
                        column.DbColumnName = "GuidCode";
                        column.TableName = nameof(DeviceInfo);
                        Db.DbMaintenance.AddColumn(nameof(DeviceInfo), column);
                    }
                    if (!Db.DbMaintenance.IsAnyColumn(nameof(DeviceInfo), "DeviceSerialNo"))
                    {
                        var column = new DbColumnInfo();
                        column.IsNullable = true;
                        column.DbColumnName = "DeviceSerialNo";
                        column.TableName = nameof(DeviceInfo);
                        Db.DbMaintenance.AddColumn(nameof(DeviceInfo), column);
                    }
                    if (!Db.DbMaintenance.IsAnyColumn(nameof(DeviceInfo), "CreatorName"))
                    {
                        var column = new DbColumnInfo();
                        column.IsNullable = true;
                        column.DbColumnName = "CreatorName";
                        column.TableName = nameof(DeviceInfo);
                        Db.DbMaintenance.AddColumn(nameof(DeviceInfo), column);
                    }
                    if (!Db.DbMaintenance.IsAnyColumn(nameof(DeviceInfo), "CreateTime"))
                    {
                        var column = new DbColumnInfo();
                        column.IsNullable = true;
                        column.DbColumnName = "CreateTime";
                        column.TableName = nameof(DeviceInfo);
                        Db.DbMaintenance.AddColumn(nameof(DeviceInfo), column);
                    }
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("数据库初始化异常", ex);
            }
        }


        public int Delete(DeviceInfo entity)
        {
            return Db.Deleteable(entity).ExecuteCommand();
        }

        public Task<List<DeviceInfo>> FindAllAsycn(string keyword = null)
        {
            if(string.IsNullOrEmpty(keyword))
            { 
                return Db.Queryable<DeviceInfo>().ToListAsync();
            }
            else
            {
                var keys = keyword.Split(new string[] { " ", ":", "：", "|" }, StringSplitOptions.RemoveEmptyEntries).ToList();
                if (keys.Count() > 0)
                {
                    if (keys.Count() > 1)
                    {
                        return Db.Queryable<DeviceInfo>()
                                 .Where(p => p.ProjectName.Contains(keys[0]) || p.DeviceName.Contains(keys[1]))
                                 .OrderBy(p => p.LastTime, OrderByType.Desc)
                                 .ToListAsync();
                    }
                    else
                    {
                        return Db.Queryable<DeviceInfo>()
                            .Where(p => p.ProjectName.Contains(keys[0]) || p.DeviceName.Contains(keys[0]))
                            .OrderBy(p => p.LastTime, OrderByType.Desc)
                            .ToListAsync();
                    }
                }
                return Db.Queryable<DeviceInfo>()
                    .Where(p => p.ProjectName.Contains(keyword) || p.DeviceName.Contains(keyword))
                    .OrderBy(p => p.LastTime, OrderByType.Desc)
                    .ToListAsync();
            }
        }

        public Task<List<DeviceInfo>> FindAllAsycn(string keyword, int pageNumber, int pageSize, RefAsync<int> totalNumber)
        {
            var keys = keyword.Split(new string[] { " ", ":", "：", "|" }, StringSplitOptions.RemoveEmptyEntries).ToList();
            if (keys.Count() > 0)
            {
                if (keys.Count() > 1)
                {
                    return Db.Queryable<DeviceInfo>()
                             .Where(p => p.ProjectName.Contains(keys[0]) || p.DeviceName.Contains(keys[1]))
                             .OrderBy(p => p.LastTime, OrderByType.Desc)
                             .ToPageListAsync(pageNumber, pageSize, totalNumber);
                }
                else
                {
                    return Db.Queryable<DeviceInfo>()
                        .Where(p => p.ProjectName.Contains(keys[0]) || p.DeviceName.Contains(keys[0]))
                        .OrderBy(p => p.LastTime, OrderByType.Desc)
                        .ToPageListAsync(pageNumber, pageSize, totalNumber);
                }
            }
            return Db.Queryable<DeviceInfo>()
                .Where(p => p.ProjectName.Contains(keyword) || p.DeviceName.Contains(keyword))
                .OrderBy(p => p.LastTime, OrderByType.Desc)
                .ToPageListAsync(pageNumber, pageSize, totalNumber);
        }

        public DeviceInfo GetById(int id)
        {
            return Db.Queryable<DeviceInfo>().InSingle(id);
        }

        public int Insert(DeviceInfo entity)
        {
            return Db.Insertable<DeviceInfo>(entity).ExecuteCommand();
        }

        public int Update(DeviceInfo entity)
        {
            //根据主键进行更新
            return Db.Updateable<DeviceInfo>(entity).ExecuteCommand();
        }
        /// <summary>
        /// 查找最近连接的设备
        /// </summary>
        /// <param name="count">要查找的数量</param>
        /// <returns></returns>
        public Task<List<DeviceInfo>> FindLatestAsycn(int count)
        {
            return Db.Queryable<DeviceInfo>().OrderBy(p => p.LastTime, OrderByType.Desc).Take(count).ToListAsync();
        }
        /// <summary>
        /// 按页查找最近连接的设备
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public Task<List<DeviceInfo>> FindPageLatestAsycn(int pageIndex, int pageSize, RefAsync<int> totalNumber)
        {
            return Db.Queryable<DeviceInfo>().OrderBy(p => p.LastTime, OrderByType.Desc).ToPageListAsync(pageIndex, pageSize, totalNumber);
        }

        public Task<List<DeviceInfo>> FindAllAsycn(Expression<Func<DeviceInfo, bool>> expression)
        {
            return Db.Queryable<DeviceInfo>().Where(expression).ToListAsync();
        }

        public Task<bool> AnyAsycn(Expression<Func<DeviceInfo, bool>> expression)
        {
            return Db.Queryable<DeviceInfo>().Where(expression).AnyAsync();
        }
    }
}
